package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.UserDetailVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserDetailMapper {
    @Select("SELECT \n" +
            "    u.user_id AS userId, \n" +
            "    u.username, \n" +
            "    up.real_name AS realName, \n" +
            "    up.avatar, \n" +
            "    (SELECT COUNT(*) FROM answer_records WHERE user_id = u.user_id) AS totalAnswers, \n" +
            "    (SELECT COUNT(*) FROM wrong_questions WHERE user_id = u.user_id) AS wrongAnswers, \n" +
            "    (SELECT ROUND(SUM(is_correct)/COUNT(*)*100, 2) FROM answer_records WHERE user_id = u.user_id) AS correctRate, \n" +
            "    (SELECT COUNT(*) FROM posts WHERE user_id = u.user_id AND status = 1) AS postCount, \n" +
            "    (SELECT COUNT(*) FROM post_likes WHERE user_id = u.user_id) AS likeCount, \n" +
            "    (SELECT COUNT(*) FROM post_favorites WHERE user_id = u.user_id) AS favoriteCount, \n" +
            "    (SELECT COUNT(*) FROM mock_exams WHERE user_id = u.user_id) AS examCount \n" +
            "FROM users u \n" +
            "LEFT JOIN user_profiles up ON u.user_id = up.user_id \n" +
            "WHERE u.user_id = #{userId};")
    UserDetailVO getUserDetail(@Param("userId") Long userId);
}
